﻿using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using Com.Ompchina.Lib.DBHelper;
using IDAL.Cms;
using IDAL;
using Model.Cms;
using Model.Config;
using Model.Base;
using Model.AuthModel;
namespace SQLServerDAL.Cms
{
    /// <summary>
    /// 数据层，内容管理，新闻
    /// </summary>
    public class NewsDAL : DalBase, INews
    {
        public NewsDAL() 
        {
            Schema = "cms.";
            TableName = "News";
            ViewTableName = "V_News";
            FieldsList = new string[] { 
                "NewsID, NewsTitle, Filepath, NewsSummary, NewsContent, NewsLink, Recommend, Author, StaffID, IsOnline, OnlineTime, UpdateTime, AddTime, NewsTypeID, NewsTypeLabel, NewsTypeName"
                ,"NewsID, NewsTitle, Filepath, NewsSummary, NewsLink, Recommend, Author, StaffID, IsOnline, OnlineTime, UpdateTime, AddTime, NewsTypeID, NewsTypeLabel, NewsTypeName"
            };
        }

        #region INews 成员
        /// <summary>
        /// 新增新闻
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool InsertNews(NewsInfo info)
        {
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@NewsType",SqlDbType.Int)
                ,new SqlParameter("@NewsTitle",SqlDbType.NVarChar,100)
                ,new SqlParameter("@Filepath",SqlDbType.VarChar,100)
                ,new SqlParameter("@NewsSummary",SqlDbType.NVarChar,400)
                ,new SqlParameter("@NewsContent",SqlDbType.NText)
                ,new SqlParameter("@NewsLink",SqlDbType.NVarChar,200)
                ,new SqlParameter("@Recommend",SqlDbType.Char,1)
                ,new SqlParameter("@Author",SqlDbType.NVarChar,20)
                ,new SqlParameter("@StaffID",SqlDbType.Int)
                ,new SqlParameter("@IsOnline",SqlDbType.Char,1)
                ,new SqlParameter("@OnlineTime",SqlDbType.DateTime)
                ,new SqlParameter("@RtnCode",SqlDbType.Char,2)
            };
            int i = 0;
            parm[i++].Value = info.NewsType.TermID;
            parm[i++].Value = info.NewsTitle;
            parm[i++].Value = info.Filepath;
            parm[i++].Value = info.NewsSummary;
            parm[i++].Value = info.NewsContent;
            parm[i++].Value = info.NewsLink;
            parm[i++].Value = info.Recommend;
            parm[i++].Value = info.Author;
            parm[i++].Value = info.Staff.StaffID;
            parm[i++].Value = info.IsOnline;
            parm[i++].Value = info.OnlineTime;
            parm[i++].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(base.ConnectionString, CommandType.StoredProcedure, Schema + "AddNews", parm);
            return parm[parm.Length - 1].Value.ToString().Equals("80");
        }
        /// <summary>
        /// 修改新闻
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool UpdateNews(NewsInfo info)
        {
            SqlParameter[] parm = new SqlParameter[] { 
                new SqlParameter("@NewsID",SqlDbType.Int)
                ,new SqlParameter("@NewsType",SqlDbType.Int)
                ,new SqlParameter("@NewsTitle",SqlDbType.NVarChar,100)
                ,new SqlParameter("@Filepath",SqlDbType.VarChar,100)
                ,new SqlParameter("@NewsSummary",SqlDbType.NVarChar,400)
                ,new SqlParameter("@NewsContent",SqlDbType.NText)
                ,new SqlParameter("@NewsLink",SqlDbType.NVarChar,200)
                ,new SqlParameter("@Recommend",SqlDbType.Char,1)
                ,new SqlParameter("@Author",SqlDbType.NVarChar,20)
                ,new SqlParameter("@StaffID",SqlDbType.Int)
                ,new SqlParameter("@IsOnline",SqlDbType.Char,1)
                ,new SqlParameter("@OnlineTime",SqlDbType.DateTime)
                ,new SqlParameter("@RtnCode",SqlDbType.Char,2)
            };
            int i = 0;
            parm[i++].Value = info.NewsID;
            parm[i++].Value = info.NewsType.TermID;
            parm[i++].Value = info.NewsTitle;
            parm[i++].Value = info.Filepath;
            parm[i++].Value = info.NewsSummary;
            parm[i++].Value = info.NewsContent;
            parm[i++].Value = info.NewsLink;
            parm[i++].Value = info.Recommend;
            parm[i++].Value = info.Author;
            parm[i++].Value = info.Staff.StaffID;
            parm[i++].Value = info.IsOnline;
            parm[i++].Value = info.OnlineTime;
            parm[i++].Direction = ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(base.ConnectionString, CommandType.StoredProcedure, Schema + "UpdateNews", parm);
            return parm[parm.Length - 1].Value.ToString().Equals("80");
        }

        public NewsInfo GetNewsByID(int id)
        {
            Sql = string.Format("SELECT TOP 1 {0} FROM {1} WHERE NewsID = {2}", FieldsList[0], Schema + ViewTableName, id);
            SqlDataReader dr = SqlHelper.ExecuteReader(base.ConnectionString, CommandType.Text, Sql);
            NewsInfo info = new NewsInfo();
            if (dr != null) 
            {
                if (dr.Read()) 
                {
                    int i = 0;
                    info.NewsID = dr.GetInt32(i++);
                    info.NewsTitle = dr.GetString(i++);
                    info.Filepath = dr.GetString(i++);
                    info.NewsSummary = dr.GetString(i++);
                    info.NewsContent = dr.GetString(i++);
                    info.NewsLink = dr.GetString(i++);
                    info.Recommend = dr.GetString(i++);
                    info.Author = dr.GetString(i++);
                    info.Staff = new StaffInfo();
                    info.Staff.StaffID = dr.GetInt32(i++);
                    info.IsOnline = dr.GetString(i++);
                    info.OnlineTime = dr.GetDateTime(i++);
                    info.UpdateTime = dr.GetDateTime(i++);
                    info.AddTime = dr.GetDateTime(i++);
                    info.NewsType = new TermInfo();
                    info.NewsType.TermID = dr.GetInt32(i++);
                    info.NewsType.TermLabel = dr.GetString(i++);
                    info.NewsType.TermName = dr.GetString(i++);
                }
                dr.Close();
            }
            return info;
        }

        public IList<NewsInfo> ListNews(NewsCondition condition)
        {
            SqlBuilder = new StringBuilder("SELECT ");
            if (condition.Top > 0)
                SqlBuilder.AppendFormat(" TOP({0}) ", condition.Top);
            SqlBuilder.AppendFormat(" {0} FROM {1} AS A WHERE 1 = 1 ", FieldsList[1], Schema + ViewTableName);
            if (condition.NewsTypeID > 0)
                SqlBuilder.AppendFormat(" AND NewsTypeID = {0}", condition.NewsTypeID);
            if (!string.IsNullOrEmpty(condition.NewsTypeLabel))
                SqlBuilder.AppendFormat(" AND NewsTypeLabel = '{0}'", condition.NewsTypeLabel);
            if (!string.IsNullOrEmpty(condition.IsOnline))
                SqlBuilder.AppendFormat(" AND IsOnline = '{0}'", condition.IsOnline);
            if (condition.TimeType.Equals(ListTime.Online))
                SqlBuilder.Append(" AND DATEDIFF(mi, OnlineTime, GETDATE()) > 0 ");
            if (condition.RangeType.Equals(ListRange.Range))
                SqlBuilder.AppendFormat(" AND OnlineTime BETWEEN '{0}' AND DATEADD(d, 1, '{1}') ", condition.StartTime, condition.StopTime);
            if (condition.ParentTermID > 0)
                SqlBuilder.AppendFormat(" AND EXISTS(SELECT 1 FROM cms.Term WHERE ParentTerm = {0} AND TermID = A.NewsTypeID)", condition.ParentTermID);
            if (!string.IsNullOrEmpty(condition.OrderBy))
                SqlBuilder.AppendFormat(" ORDER BY {0} ", condition.OrderBy);
            SqlDataReader dr = SqlHelper.ExecuteReader(base.ConnectionString, CommandType.Text, SqlBuilder.ToString());
            List<NewsInfo> list = new List<NewsInfo>();
            if (dr != null)
            {
                while (dr.Read())
                {
                    NewsInfo info = new NewsInfo();
                    int i = 0;
                    info.NewsID = dr.GetInt32(i++);
                    info.NewsTitle = dr.GetString(i++);
                    info.Filepath = dr.GetString(i++);
                    info.NewsSummary = dr.GetString(i++);
                    info.NewsLink = dr.GetString(i++);
                    info.Recommend = dr.GetString(i++);
                    info.Author = dr.GetString(i++);
                    info.Staff = new StaffInfo();
                    info.Staff.StaffID = dr.GetInt32(i++);
                    info.IsOnline = dr.GetString(i++);
                    info.OnlineTime = dr.GetDateTime(i++);
                    info.UpdateTime = dr.GetDateTime(i++);
                    info.AddTime = dr.GetDateTime(i++);
                    info.NewsType = new TermInfo();
                    info.NewsType.TermID = dr.GetInt32(i++);
                    info.NewsType.TermLabel = dr.GetString(i++);
                    info.NewsType.TermName = dr.GetString(i++);
                    list.Add(info);
                }
                dr.Close();
            }
            return list;
        }

        public IList<NewsInfo> ListNews(NewsCondition condition, out int recordCount)
        {
            Wheres = new StringBuilder(" 1 = 1 ");
            if (condition.NewsTypeID > 0)
                Wheres.AppendFormat(" AND NewsTypeID = {0}", condition.NewsTypeID);
            if (!string.IsNullOrEmpty(condition.NewsTypeLabel))
                Wheres.AppendFormat(" AND NewsTypeLabel = '{0}'", condition.NewsTypeLabel);
            if (!string.IsNullOrEmpty(condition.IsOnline))
                Wheres.AppendFormat(" AND IsOnline = '{0}'", condition.IsOnline);
            if (condition.TimeType.Equals(ListTime.Online))
                Wheres.Append(" AND DATEDIFF(mi, OnlineTime, GETDATE()) > 0 ");
            if (condition.RangeType.Equals(ListRange.Range))
                Wheres.AppendFormat(" AND OnlineTime BETWEEN '{0}' AND DATEADD(d, 1, '{1}') ", condition.StartTime, condition.StopTime);
            recordCount = PageListCounter(Schema + ViewTableName, Wheres.ToString());
            SqlDataReader dr = base.PageListRecord(FieldsList[1], Schema + ViewTableName, Wheres.ToString()
                , condition.OrderBy, condition.PageIndex, condition.PageSize);
            List<NewsInfo> list = new List<NewsInfo>();
            if (dr != null)
            {
                while (dr.Read())
                {
                    NewsInfo info = new NewsInfo();
                    int i = 0;
                    info.NewsID = dr.GetInt32(i++);
                    info.NewsTitle = dr.GetString(i++);
                    info.Filepath = dr.GetString(i++);
                    info.NewsSummary = dr.GetString(i++);
                    info.NewsLink = dr.GetString(i++);
                    info.Recommend = dr.GetString(i++);
                    info.Author = dr.GetString(i++);
                    info.Staff = new StaffInfo();
                    info.Staff.StaffID = dr.GetInt32(i++);
                    info.IsOnline = dr.GetString(i++);
                    info.OnlineTime = dr.GetDateTime(i++);
                    info.UpdateTime = dr.GetDateTime(i++);
                    info.AddTime = dr.GetDateTime(i++);
                    info.NewsType = new TermInfo();
                    info.NewsType.TermID = dr.GetInt32(i++);
                    info.NewsType.TermLabel = dr.GetString(i++);
                    info.NewsType.TermName = dr.GetString(i++);
                    list.Add(info);
                }
                dr.Close();
            }
            return list;
        }

        #endregion
    }
}
